global restrdir "U:\data"
global public "R:\Public"
global sswdata "R:\Public\XWave\SocialSecurityWealth2010\built\stata"
global rand "R:\Public\Contributions\Rand\RandHRS2016V2\stata"
global output "U:\output"

*-------------------------------------------------------------------------------
*This program computes age-specific estimates of household wealth (non-pension)
*and DC accounts for w2010 respondents with non-frozen DB plans in the 
*private sector. 
*-------------------------------------------------------------------------------

*-------------------------------------------------------------------------------
*Create tempfiles to store public data vars
*-------------------------------------------------------------------------------

use $public/XWave/PenWealthImp2010/built/stata/PWI10A_R, clear
gen double temp1 = real(HHID)
gen double temp2 = real(PN)
gen double respondent = temp1*1000 + temp2

keep respondent DBFlag_10 HHID PN
tempfile pwi10a_r
save `pwi10a_r'

*-------------------------------------------------------------------------------
* 2010 DB wealth and earnings projection from PEP 
* worker-plan-projected year pseudo panel
*-------------------------------------------------------------------------------

*Pension wealth and earnings projections from PEP (private sector workers)
import delimited $restrdir\penval_2010_earnadj_22.csv, clear 
rename maxnrervd dbwealth

*Get PEP coders' flag for frozen/CB converted plans based off SPD's/F5500's  
tempfile codeids
preserve
	keep codeid
	duplicates drop 
	rename codeid CodingID
	merge 1:m CodingID using $restrdir/PlanInfo2010ReleaseV0, ///
	keepus(FrozenPlan)	
	keep if _merge==3
	duplicates drop	
	rename CodingID codeid
	save `codeids', replace
restore	

merge m:1 codeid using `codeids', keepus(FrozenPlan)
drop _merge

*Merge with unrestricted pension data 
merge m:1 respondent using `pwi10a_r' 
tab DBFlag_10 _merge 
//unmerged obs are either pure DC or public sector employees (i.e. excluded from pension calculator input)
//1 record does not have a codeid
keep if _merge==3
drop _merge

*Remove respondent-years in plans coded as (Frozen/CB converted) by HRS
drop if FrozenPlan==1

*Drop combo plans
drop if codeid>5000

*Keep unique respondents
keep HHID PN
duplicates drop 
tempfile respondents
save `respondents'

*-------------------------------------------------------------------------------
* Get wealth measures in 2010 wave from RAND file
*-------------------------------------------------------------------------------

use hhid pn h10atotf h10atoth h10anethb r10dcbal1 s10dcbal1 r10dcbal2 s10dcbal2 ///
r10dcbal3 s10dcbal3 r10dcbal4 s10dcbal4 r10wthh r10agey_b using $rand\randhrs1992_2016v2, clear
duplicates drop hhid pn, force
rename r10agey_b age_2010
rename h10atotf net_nonh_w //net non-housing financial wealth
rename h10atoth net_house1_w //net value of primary residence
rename h10anethb net_house2_w //net value of secondary residence
gen net_w = net_nonh_w + net_house1_w + net_house2_w

*Missings and 0's coded as 0
forvalues j = 1/4{
    replace r10dcbal`j'= 0 if inlist(r10dcbal`j',.b,.q,.w,.s,.u,.v,.d,.r)
	replace s10dcbal`j'= 0 if inlist(s10dcbal`j',.b,.q,.w,.s,.u,.v,.d,.r)
}

gen dca = r10dcbal1 + r10dcbal2 + r10dcbal3 + r10dcbal4 + s10dcbal1 + ///
s10dcbal2 + s10dcbal3 + s10dcbal4 // total (hh) defined contribution wealth

rename hhid HHID
rename pn PN
keep HHID PN net_w dca r10wthh age_2010

*Merge with pvt sector non-frozen DB eligible workers
merge 1:1 HHID PN using `respondents'
assert _merge!=2 //all R's in DB group should be found in the RAND file
keep if _merge==3
drop _merge

*Weighted age shares (51-59) to export for model simulations
matrix age_shares=J(9,3,.)
local r = 1
forvalues a = 51/59{
	gen temp = age_2010==`a'
	qui sum temp if inrange(age_2010,51,59) [aw=r10wthh]
	matrix age_shares[`r',2]=r(mean)
	matrix age_shares[`r',3]=`a'
	qui count if age_2010==`a'
	matrix age_shares[`r',1]=r(N)	
	drop temp
	local r = `r'+1
}

*Export age shares
preserve
	clear
	svmat age_shares
	rename age_shares1 N
	rename age_shares2 sample_fraction
	rename age_shares3 age
	export delimited using $output/age_shares.csv, replace
restore

*Log of positive wealth
gen lnnet_w = ln(net_w) 
gen lndca = ln(dca)

*Indicator for 0 dc wealth
gen dc0 = dca==0

matrix A_param = J(9,3,.)
matrix W_param = J(9,4,.)

local j = 1
forvalues a = 51/59{
    qui sum lnnet_w if age_2010 == `a' [aw=r10wthh]
	matrix A_param[`j',1] = r(mean)
	matrix A_param[`j',2] = r(sd)
	matrix A_param[`j',3] = r(N)	
	
    qui sum lndca if age_2010 == `a' [aw=r10wthh]
	matrix W_param[`j',1] = r(mean)
	matrix W_param[`j',2] = r(sd)
	matrix W_param[`j',3] = r(N)	
	gen temp = lndca!=.
	qui sum temp if age==`a' [aw=r10wthh]
	matrix W_param[`j',4] = r(mean)
	
	local j = `j'+1
	drop temp
}

*Ouput param tables
preserve
	clear
	svmat A_param
	rename A_param1 mu
	rename A_param2 sigma
	rename A_param3 N
	gen age = _n+50
	order N age mu sigma 
	export delimited $output/A_params_c.csv, replace
restore

preserve
	clear
	svmat W_param
	rename W_param1 mu
	rename W_param2 sigma
	rename W_param3 N
	rename W_param4 share_0dc
	gen age = _n+50
	order N age mu sigma share_0dc
	export delimited $output/W_params_c.csv, replace
restore

*Output data for MLE estimation of join wealth distribution
drop if net_w<=0 //not modeling negative net worth
replace lndca = 0 if lndca==.
keep age r10wthh lnnet_w lndca
order age_2010 lndca lnnet_w r10wthh
export delimited using "$restrdir/logwealthdata", replace



